/**
 * 
 */
package cn.chendd.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author chendd
 *
 */
public class ExcelUtil {

	public static int getExcelVesion(String suffix){
		return "xlsx".equalsIgnoreCase(suffix) ? 2007 : 2003;
	}
	
	public static Workbook getWorkbookByExcelFile(File file) throws IOException{
		Workbook workbook = null;
		String fileName = file.getName();
		String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
		if(getExcelVesion(suffix) == 2007){
			workbook = new XSSFWorkbook(new FileInputStream(file));
		} else{
			workbook = new HSSFWorkbook(new FileInputStream(file));
		}
		return workbook;
	}
	
	public static List<List<String>> getListByExcel(File file , int startRowIndex) throws IOException {
		Workbook workbook = ExcelUtil.getWorkbookByExcelFile(file);
		Sheet sheet = workbook.getSheetAt(0);
		int totalRow = sheet.getPhysicalNumberOfRows();//总记录行数
		List<List<String>> dataList = new ArrayList<List<String>>();
		for (int i= startRowIndex ; i < totalRow ; i++) {
			Row row = sheet.getRow(i);
			if(row == null){
				continue;
			}
			int cells = row.getLastCellNum();
			Cell firstCell = row.getCell(0);
			if(firstCell == null){
				firstCell = row.createCell(0);
			}
			String tempValue = getCellValue(firstCell);
			//判断某一行的第一个单元格是否为null或者""，为空则本行数据被跳过
			if(StringUtils.isEmpty(tempValue)){
				continue;
			}
			
			List<String> lineList = new ArrayList<String>();
			for(int y=0 ; y < cells ; y++){
				Cell cell = row.getCell(y);
				if(cell == null){
					cell = row.createCell(y);
				}
				String value = getCellValue(cell);
				lineList.add(value);
			}
			dataList.add(lineList);
		}
		
		return dataList;
	}
	
	public static List<List<String>> getListByExcel(File file) throws IOException {
		
		return getListByExcel(file , 0);
	}
	
	public static String getCellValue(Cell cell){
		
		String value = null;
		switch (cell.getCellType()) {
			case Cell.CELL_TYPE_BLANK:
				//value = "";
				value = null;
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				value = String.valueOf(cell.getBooleanCellValue());
				break;
			case Cell.CELL_TYPE_FORMULA:
				value = String.valueOf(cell.getCellFormula());
				break;
			case Cell.CELL_TYPE_NUMERIC:
				if (DateUtil.isCellDateFormatted(cell)) {
					Date date = cell.getDateCellValue();
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					value = sdf.format(date);
				} else {
					double val = cell.getNumericCellValue();
					BigDecimal bd = new BigDecimal(val);
					// 判断小数点的位数,格式化成原有小数点的长度
					String s = String.valueOf(val);
					int index = s.indexOf(".");
					if (index != -1) {
						index = s.length() - index - 1;
					} else {
						index = 0;
					}
					DecimalFormat dcf = new DecimalFormat("#");
					dcf.setMaximumFractionDigits(index);
					value = dcf.format(bd.doubleValue());
				}
				break;
			case Cell.CELL_TYPE_STRING:
				value = cell.getStringCellValue();
				break;
			default:
				value = cell.getRichStringCellValue().getString();
				break;
		}
		
		return value;
	}

}
